﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Huirui.Cavan.Domain.Seedwork;
using Huirui.Cavan.Domain.MainBoundedContext.SearchModule;
using Huirui.Cavan.Domain.MainBoundedContext.StockModule;
using Huirui.Cavan.Data.Seedwork;
using Huirui.Cavan.Domain.MainBoundedContext.ReportModule;
using Huirui.Cavan.Core.Extension;
using Spring.Data.Common;
using System.Data;
using Huirui.Cavan.Domain.MainBoundedContext.SystemModule;
using Huirui.Cavan.Core.Helper;
using NHibernate;

namespace Huirui.Cavan.Infrastructure.Data.MainBoundedContext.StockModule
{
    public class StockRepository : Repository<Stock, StockSearch>, IStockRepository
    {
        public IList<StockByPurchaseOrderReport> GetStocksByPurchaseOrderReport(DateTime? startDate, DateTime? endDate, string purchaseOrderCode, string supplierCode, string stockInStatues)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT s.Code, s2.Name AS SupplierName, s.Name, po.DeliveryDate, pod.Number, pod.UnStockInNumber, wh.[Address], po.Code AS PurchaseOrderCode, ISNULL(s.Specification, '') AS Specification, ISNULL(pod.TaxRate, 0) AS TaxRate, s.StandardPurchasePrice, ISNULL(ut.FromNumber, 1) AS FromNumber, ISNULL(ut.ToNumber, 1) AS ToNumber ");
            sql.Append("FROM Stock s INNER JOIN PurchaseOrderDetail pod WITH (NOLOCK) ON pod.StockCode = s.Code ");
            sql.Append("INNER JOIN PurchaseOrder po WITH (NOLOCK) ON po.Code = pod.PurchaseOrderCode ");
            sql.Append("INNER JOIN WareHouse wh WITH (NOLOCK) ON wh.Code = s.WareHouseCode ");
            sql.Append("INNER JOIN Supplier s2 WITH (NOLOCK) ON s2.Code = po.SupplierCode ");
            sql.Append("LEFT JOIN UnitTransfer ut WITH(NOLOCK) ON ut.ID = s.UnitTransferID ");
            sql.Append("WHERE  1 = 1 ");
            if (startDate.HasValue && endDate.HasValue)
            {
                sql.Append("AND  po.CreateDate >= @StartDate AND po.CreateDate <= @EndDate ");
            }
            if (purchaseOrderCode.IsNotEmpty())
            {
                sql.Append("AND po.Code = @PurchaseOrderCode ");
            }
            if (supplierCode.IsNotEmpty())
            {
                sql.Append("AND po.SupplierCode = @SupplierCode ");
            }
            if (stockInStatues.IsNotEmpty())
            {
                sql.Append("AND po.StockInStatus in (@StockInStatus)");
            }
            IDbParameters paras = this.AdoTemplate.CreateDbParameters();

            if (startDate.HasValue && endDate.HasValue)
            {
                paras.Add("StartDate", DbType.DateTime).Value = startDate.Value;
                paras.Add("EndDate", DbType.DateTime).Value = endDate.Value;
            }
            if (supplierCode.IsNotEmpty())
            {
                paras.Add("SupplierCode", DbType.String).Value = supplierCode;
            }
            if (purchaseOrderCode.IsNotEmpty())
            {
                paras.Add("PurchaseOrderCode", DbType.String).Value = purchaseOrderCode;
            }
            if (stockInStatues.IsNotEmpty())
            {
                paras.Add("StockInStatus", DbType.String).Value = stockInStatues;
            }
            return this.AdoTemplate.QueryWithRowMapper<StockByPurchaseOrderReport>(CommandType.Text, sql.ToString(), new StockByPurchaseOrderReportMapper(), paras);
        }

        public IList<StockByPurchaseReport> GetStocksByPurchaseReport(DateTime? startDate, DateTime? endDate, string purchaseCode, string stockCode)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT p.PurchaseDate, p.Code AS PurchaseCode, s.Name, siod.Number, siod.UnitCost, s.Code, ISNULL(siod.TaxRate, 0) AS TaxRate ");
            sql.Append("FROM Stock s WITH (NOLOCK) ");
            sql.Append("INNER JOIN StockInOutDetail siod WITH(NOLOCK) ON siod.StockCode = s.Code ");
            sql.Append("INNER JOIN StockInOut sio WITH (NOLOCK) ON sio.Code = siod.StockInCode AND sio.[Type] =  " + SystemIdentifier.DefaultStockInOutType.Purchase.GetHashCode().ToString() + " ");
            sql.Append("INNER JOIN Purchase p WITH(NOLOCK) ON p.Code = sio.SourceCode ");
            sql.Append("WHERE  1 = 1 ");
            if (startDate.HasValue && endDate.HasValue)
            {
                sql.Append("AND  p.PurchaseDate >= @StartDate AND p.PurchaseDate <= @EndDate ");
            }
            if (purchaseCode.IsNotEmpty())
            {
                sql.Append("AND p.Code = @PurchaseCode ");
            }
            if (stockCode.IsNotEmpty())
            {
                sql.Append("AND s.Code = @stockCode ");
            }
            IDbParameters paras = this.AdoTemplate.CreateDbParameters();

            if (startDate.HasValue && endDate.HasValue)
            {
                paras.Add("StartDate", DbType.DateTime).Value = startDate.Value;
                paras.Add("EndDate", DbType.DateTime).Value = endDate.Value;
            }
            if (purchaseCode.IsNotEmpty())
            {
                paras.Add("PurchaseCode", DbType.String).Value = purchaseCode;
            }
            if (stockCode.IsNotEmpty())
            {
                paras.Add("StockCode", DbType.String).Value = stockCode;
            }
           
            return this.AdoTemplate.QueryWithRowMapper<StockByPurchaseReport>(CommandType.Text, sql.ToString(), new StockByStockInReportMapper(), paras);
        }

        public IList<StockBySalesOrderReport> GetStocksBySalesOrderReport(DateTime? startDate, DateTime? endDate, string salesOrderCode, string customerCode, string stockOutStatues)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT s.Code, c.Name AS CustomerName, s.Name, so.DeliveryDate, sod.Number, sod.UnStockOutNumber, wh.[Address], so.Code AS SalesOrderCode, ISNULL(s.Specification, '') AS Specification, ISNULL(sod.TaxRate, 0) AS TaxRate, s.StandardSalesPrice, 0 AS DiscountRate, ");
            sql.Append("(CAST(ut.FromNumber AS VARCHAR(10)) + su.Name + '/' + CAST(ut.ToNumber AS VARCHAR(10)) + su2.Name) AS UnitTransfer ");
            sql.Append("FROM Stock s INNER JOIN SalesOrderDetail sod WITH (NOLOCK) ON sod.StockCode = s.Code ");
            sql.Append("INNER JOIN SalesOrder so WITH (NOLOCK) ON so.Code = sod.SalesOrderCode ");
            sql.Append("INNER JOIN WareHouse wh WITH (NOLOCK) ON wh.Code = s.WareHouseCode ");
            sql.Append("INNER JOIN Customer c WITH (NOLOCK) ON c.Code = so.CustomerCode ");
            sql.Append("LEFT JOIN UnitTransfer ut WITH (NOLOCK) ON ut.ID = s.UnitTransferID ");
            sql.Append("LEFT JOIN StockUnit su WITH (NOLOCK) ON su.ID = ut.FromUnitID ");
            sql.Append("LEFT JOIN StockUnit su2 WITH (NOLOCK) ON su2.ID = ut.ToUnitID ");
            sql.Append("WHERE  1 = 1 ");
            if (startDate.HasValue && endDate.HasValue)
            {
                sql.Append("AND  so.CreateDate >= @StartDate AND so.CreateDate <= @EndDate ");
            }
            if (salesOrderCode.IsNotEmpty())
            {
                sql.Append("AND so.Code = @SalesOrderCode ");
            }
            if (customerCode.IsNotEmpty())
            {
                sql.Append("AND so.CustomerCode = @CustomerCode ");
            }
            if (stockOutStatues.IsNotEmpty())
            {
                sql.Append("AND so.StockOutStatus in (@StockOutStatus)");
            }
            IDbParameters paras = this.AdoTemplate.CreateDbParameters();

            if (startDate.HasValue && endDate.HasValue)
            {
                paras.Add("StartDate", DbType.DateTime).Value = startDate.Value;
                paras.Add("EndDate", DbType.DateTime).Value = endDate.Value;
            }
            if (customerCode.IsNotEmpty())
            {
                paras.Add("CustomerCode", DbType.String).Value = customerCode;
            }
            if (salesOrderCode.IsNotEmpty())
            {
                paras.Add("SalesOrderCode", DbType.String).Value = salesOrderCode;
            }
            if (stockOutStatues.IsNotEmpty())
            {
                paras.Add("StockOutStatues", DbType.String).Value = stockOutStatues;
            }
            return this.AdoTemplate.QueryWithRowMapper<StockBySalesOrderReport>(CommandType.Text, sql.ToString(), new StockBySalesOrderReportMapper(), paras);
        }

        public IList<StockBySalesReport> GetStocksBySalesReport(DateTime? startDate, DateTime? endDate, string salesCode, string stockCode)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT s.Code AS SalesCode, s.SalesDate, sk.Name, sd.Number, sd.UnitCost, sk.Code ");
            sql.Append("FROM Sales s WITH (NOLOCK) ");
            sql.Append("INNER JOIN SalesDetail sd WITH(NOLOCK) ON sd.SalesCode = s.Code ");
            sql.Append("INNER JOIN Stock sk WITH (NOLOCK) ON sd.StockCode = sk.Code ");
            sql.Append("WHERE  1 = 1 ");
            if (startDate.HasValue && endDate.HasValue)
            {
                sql.Append("AND  s.SalesDate >= @StartDate AND s.SalesDate <= @EndDate ");
            }
            if (salesCode.IsNotEmpty())
            {
                sql.Append("AND s.Code = @SalesCode ");
            }
            if (stockCode.IsNotEmpty())
            {
                sql.Append("AND sk.Code = @StockCode ");
            }
            IDbParameters paras = this.AdoTemplate.CreateDbParameters();

            if (startDate.HasValue && endDate.HasValue)
            {
                paras.Add("StartDate", DbType.DateTime).Value = startDate.Value;
                paras.Add("EndDate", DbType.DateTime).Value = endDate.Value;
            }
            if (salesCode.IsNotEmpty())
            {
                paras.Add("SalesCode", DbType.String).Value = salesCode;
            }
            if (stockCode.IsNotEmpty())
            {
                paras.Add("StockCode", DbType.String).Value = stockCode;
            }
            return this.AdoTemplate.QueryWithRowMapper<StockBySalesReport>(CommandType.Text, sql.ToString(), new StockBySalesReportMapper(), paras);
        }

        public IList<StockByWareHouseReport> GetStocksByWareHouseReport(string wareHouseCode, string stockCode)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT siod.WareHouseCode, siod.StockCode, SUM(siod.Number) AS LeftNumber ");
            sql.Append("FROM StockInOutDetail siod WITH(NOLOCK) ");
            sql.Append("WHERE  1 = 1 ");
            if (wareHouseCode.IsNotEmpty())
            {
                sql.Append("AND siod.WareHouseCode = @WareHouseCode ");
            }
            if (stockCode.IsNotEmpty())
            {
                sql.Append("AND siod.StockCode = @StockCode ");
            }
            sql.Append("GROUP BY siod.WareHouseCode, siod.StockCode");
            IDbParameters paras = this.AdoTemplate.CreateDbParameters();

            if (wareHouseCode.IsNotEmpty())
            {
                paras.Add("WareHouseCode", DbType.String).Value = wareHouseCode;
            }
            if (stockCode.IsNotEmpty())
            {
                paras.Add("StockCode", DbType.String).Value = stockCode;
            }
            return this.AdoTemplate.QueryWithRowMapper<StockByWareHouseReport>(CommandType.Text, sql.ToString(), new StockByWareHouseReportMapper(), paras);
        }

        public IList<Stock> GetStocksByCustomer(StockSearch search, out long total)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT s.Code,s.Name,s.Specification,s.BarCode,ISNULL(spd.UnitCost, s.StandardSalesPrice)  AS StandardSalesPrice,s.CorporationCode,s.Nature,s.[Type],s.WareHouseCode,s.StockUnitID,s.IsProxy,s.StandardPurchasePrice,s.MaxPurchasePrice,s.MinSalesPrice,s.TaxRate,s.CreateDate,s.ModifyDate,s.EditUser,s.Version,s.MinLeftNumber,s.[Status],s.OnSale,s.UnitTransferID,s.LeftNumber,ROW_NUMBER() OVER(ORDER BY s.Code) AS RowNumber FROM Stock s WITH (NOLOCK) ");
            sql.Append("INNER JOIN SalesPriceDetail spd WITH(NOLOCK) ON spd.StockCode = s.Code ");
            sql.Append("INNER JOIN SalesPriceCustomer spc WITH (NOLOCK) ON spc.SalesPriceCode = spd.SalesPriceCode ");
            sql.Append("INNER JOIN SalesPrice sp WITH (NOLOCK) ON sp.Code = spd.SalesPriceCode AND sp.StartDate < GETDATE() AND (sp.EndDate IS NULL OR sp.EndDate > GETDATE()) ");
            sql.Append("INNER JOIN Customer c WITH (NOLOCK) ON spc.CustomerCode = c.Code ");
            sql.Append("WHERE  1 = 1 ");

            if (search.CustomerName.IsNotEmpty())
            {
                sql.Append("AND c.Name = :CustomerName ");
            }

            string sqlTotal = "SELECT COUNT(*) AS total FROM ("+ sql.ToString() + ") AS T " ;

            string querySql = "WITH Stocks AS (" + sql.ToString() + ") SELECT s.* FROM Stocks s ";

            IQuery queryTotal = this.Session.CreateSQLQuery(sqlTotal).AddScalar("total", NHibernateUtil.Int32).SetParameter("CustomerName", search.CustomerName);
            total = Convert.ToInt64(queryTotal.UniqueResult());

            IQuery query = this.Session.CreateSQLQuery(querySql).AddEntity("s", typeof(Stock)).SetParameter("CustomerName", search.CustomerName);
            IList<Stock> stocks = query.List<Stock>();

            return stocks;
        }

        public IList<Stock> GetStocksBySupplier(StockSearch search, out long total)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT s.*,ROW_NUMBER() OVER(ORDER BY s.Code) AS RowNumber ");
            sql.Append("FROM   Stock s WITH (NOLOCK) ");
            sql.Append("INNER JOIN StockInOutDetail siod WITH(NOLOCK) ON siod.StockCode = s.Code ");
            sql.Append("INNER JOIN Purchase p WITH(NOLOCK) ON p.StockInOutCode = siod.StockInCode ");
            sql.Append("INNER JOIN Supplier sp WITH (NOLOCK) ON sp.Code = p.SupplierCode ");
            sql.Append("WHERE  1 = 1 ");

            if (search.CustomerName.IsNotEmpty())
            {
                sql.Append("AND sp.Name = :SupplierName ");
            }

            string sqlTotal = "SELECT COUNT(*) AS total FROM (" + sql.ToString() + ") AS T ";

            string querySql = "WITH Stocks AS (" + sql.ToString() + ") SELECT s.* FROM Stocks s ";

            IQuery queryTotal = this.Session.CreateSQLQuery(sqlTotal).AddScalar("total", NHibernateUtil.Int32).SetParameter("SupplierName", search.SupplierName);
            total = Convert.ToInt64(queryTotal.UniqueResult());

            IQuery query = this.Session.CreateSQLQuery(querySql).AddEntity("s", typeof(Stock)).SetParameter("SupplierName", search.SupplierName);
            IList<Stock> stocks = query.List<Stock>();

            return stocks;
        }
    }
}
